package JDBC;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import SwingController.CmsPresenter;
import SwingModel.CmsAnnouncement;
import SwingModel.CmsUser;

public class InstructorAnnouncementActivities {
	
	public static List<CmsAnnouncement> getListOfAnnouncement(String username, String courseID){
		int announcementID;
		String announcementTitle;
		String announcementContent;
		boolean isRead;
		List<CmsAnnouncement> listOfAnnouncements = new ArrayList<CmsAnnouncement>();
		
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("SELECT A.AnnounId, A.AnnounTitle, A.AnnounContent, A.CreatedDTM, U.IsRead FROM Announcements A, User_Announ U WHERE A.CourseId = ? AND U.Username = ? GROUP BY A.AnnounId DESC");
			
			pstmt.setString(1, courseID);
			pstmt.setString(2, username );
			
			ResultSet resultSet = pstmt.executeQuery();
			
			while(resultSet.next()){
				//get announcement information
				announcementID = resultSet.getInt("AnnounID");
				announcementTitle = resultSet.getString("AnnounTitle");
				announcementContent = resultSet.getString("AnnounContent");
				Date createdDTM = resultSet.getTimestamp("CreatedDTM");
				isRead = resultSet.getBoolean("IsRead");
				System.out.println("" + isRead);
				//add announcement entry to list of announcements
				listOfAnnouncements.add(new CmsAnnouncement(announcementID, announcementTitle, announcementContent, createdDTM, isRead)); //set isRead to 1
			}
		} catch(Exception ex){}
		
		return listOfAnnouncements;
	}
	
	public static void postAnnouncement(String courseID, CmsAnnouncement anm){
		String announcementTitle = anm.getAnnouncementTitle();
		String announcementContent = anm.getAnnouncementContent();
		//Date createdDTM = anm.getCreatedDTM();
		String username = CmsPresenter.user.getUsername();
		List<CmsUser> people = PeopleActivities.getListOfPeople(CmsPresenter.user.getSelectedCourse().getCourseID());
		
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			
			PreparedStatement pstmt1 = dCon.getPreparedStatement();
			pstmt1 = dCon.connection.prepareStatement("INSERT INTO Announcements (CourseId, AnnounTitle, AnnounContent, CreatedDTM) VALUES (?, ?, ?, Now());");
			pstmt1.setString(1, courseID);
			pstmt1.setString(2, announcementTitle);
			pstmt1.setString(3, announcementContent);
			//pstmt1.setString(4, createdDTM);
			pstmt1.execute();
			
			System.out.println("Successfully inserted announcement into Announcements table");
				
			PreparedStatement pstmt2 = dCon.getPreparedStatement();
			pstmt2 = dCon.connection.prepareStatement("SELECT MAX(AnnounId) from Announcements;");
			ResultSet resultSet = pstmt2.executeQuery();
			
			if(resultSet.next()){
				int insertedAnnounId = resultSet.getInt(1);;
				System.out.println("Successefully inserted! The inserted announcement Id is " + insertedAnnounId);
				
				//Insert into student announcements
				
				//Assign announcement <-- instructor
				PreparedStatement pstmt3 = dCon.getPreparedStatement();
				pstmt3 = dCon.connection.prepareStatement("INSERT INTO User_Announ (AnnounId, Username, IsRead) VALUES (?, ?, 1);");
				pstmt3.setInt(1, insertedAnnounId);
				pstmt3.setString(2, username);		
				pstmt3.execute();
				
					//Assign announcement to students
					int count = people.size();					
					PreparedStatement pstmt4 = dCon.getPreparedStatement();
					
					while(count > 0){
						String iterUsername = people.get(count-1).getUsername();
						
						if(!(username.equals(iterUsername))){
							pstmt4 = dCon.connection.prepareStatement("INSERT INTO User_Announ (AnnounId, Username, IsRead) VALUES (?, ?, 0);");
							pstmt4.setInt(1, insertedAnnounId);
							pstmt4.setString(2, iterUsername);
							pstmt4.execute();
						}
						count--;
					}
					
					System.out.println("Announcement successfully created!");

			} else {
				System.out.println("\nUnsuccessful query retrieving the inserted announcement ID!\n");
			}
			dCon.close();
		} catch(Exception ex){
			System.out.println("\nUnsuccessful query while inserting a new announcement into the Announcements table!\n");
			System.out.println("Error Message: " + ex.toString());
		}
	}
	
	public static void deleteAnnouncement(String userID, int anmID){
		
		//userID is not needed
		try {
			System.out.println("Delete Announcement: " + anmID);
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("DELETE FROM User_Announ WHERE AnnounId = ?;");
			pstmt.setInt(1, anmID);
			pstmt.execute();
			
			PreparedStatement pstmt2 = dCon.getPreparedStatement();
			pstmt2 = dCon.connection.prepareStatement("DELETE FROM Announcements WHERE AnnounId = ?;");
			pstmt2.setInt(1, anmID);
			pstmt2.execute();
			
			dCon.close();
			} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Error: " + e.getMessage().toString());
		}
	}

}
